import psycopg2
import configparser
import os


def getConfig(section, key):
    try:
        config = configparser.ConfigParser()
        path = os.path.split(os.path.realpath(__file__))[0] + '/value.conf'
        config.read(path)
        return config.get(section, key)
    except configparser.NoOptionError:
        print("配置文件中不存在key为{key}的值".format(key=key))
        return None


def __execute_sql(sql="show server_version"):
    database = getConfig("postgresql", "database")
    user = getConfig("postgresql", "user")
    password = getConfig("postgresql", "password")
    host = getConfig("postgresql", "host")
    port = getConfig("postgresql", "port")
    conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    """:type : psycopg2.extensions.connection"""
    print("Opened database successfully")
    cur = conn.cursor()
    """:type : psycopg2.extensions.cursor"""
    cur.execute(sql)
    conn.commit()
    conn.close()
    return "success"


def __create_seq_sql(tablename, rowname):
    seq_name = "{tablename}_{rowname}_seq".format(tablename=tablename, rowname=rowname)
    sql = """CREATE SEQUENCE "{seq_name}" 
      INCREMENT 1
      MINVALUE  1
      MAXVALUE 2147483647
      START 1
      CACHE 1""".format(seq_name=seq_name)
    return sql


def __create_table_sql(table_name, rowsdict={}):
    gid = "gid int4 NOT NULL,".format(table_name=table_name)
    geom = "geom geometry,"
    rows = ""
    for row in rowsdict:
        rows += "{row} {type},\n".format(row=row, type=__typeChange(rowsdict[row]))
    pkey = "CONSTRAINT {table_name}_pkey PRIMARY KEY (gid)".format(table_name=table_name)
    sql = """create table {table_name}(\n{gid}\n{rows}{geom}\n{pkey});""".format(table_name=table_name, gid=gid,
                                                                                 geom=geom,
                                                                                 pkey=pkey, rows=rows)
    return sql


def __typeChange(type):
    if type == "Integer":
        return "int4"
    if type == "String":
        return "varchar(50)"


def main():
    return __execute_sql()


if __name__ == '__main__':
    table_name = "test"
    create_seq_sql = __create_seq_sql(table_name, "gid")
    print(create_seq_sql)
    print(__execute_sql(create_seq_sql))
    rowsdict = {"id": "Integer", "NAME": "String"}
    create_table_sql = __create_table_sql("test", rowsdict)
    print(create_table_sql)
    print(__execute_sql(create_table_sql))
